--    Author    : MAYANTING
--    Name      : ADM.ANA_SUST_DLMG_RATE_M_BALANCE.HQL
--    Functions :
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-05-24  MAYANTING        1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE ADM.ANA_SUST_DLMG_RATE_M_BALANCE PARTITION(DATA_DATE = '#V_DATA_DATE#')
   SELECT
           SUBSTR('#V_DATA_DATE#',1,6)      AS DATA_MONTH
          ,ORG.ORG_CODE_1                   AS ORG_CODE_1
          ,ORG.ORG_DSCR_1                   AS ORG_DSCR_1
          ,ORG.ORG_CODE_2                   AS ORG_CODE_2
          ,ORG.ORG_DSCR_2                   AS ORG_DSCR_2
          ,ORG.ORG_CODE_3                   AS ORG_CODE_3
          ,ORG.ORG_DSCR_3                   AS ORG_DSCR_3
          ,AREA.AREA_CODE_1                 AS AREA_CODE_1
          ,AREA.AREA_DSCR_1                 AS AREA_DSCR_1
          ,AREA.AREA_CODE_3                 AS AREA_CODE_2
          ,AREA.AREA_DSCR_3                 AS AREA_DSCR_2
          ,AREA.AREA_CODE_4                 AS AREA_CODE_3
          ,AREA.AREA_DSCR_4                 AS AREA_DSCR_3
          ,T.CCY                            AS CCY                  -- 币种
          ,SUM(T.LOAN_BAL          )        AS LOAN_BAL             -- 当月贷款余额
          ,SUM(T.LOAN_BAL_LM       )        AS LOAN_BAL_LM          -- 上月贷款余额
          ,SUM(T.LOAN_BAL_LY       )        AS LOAN_BAL_LY          -- 去年同期贷款余额
          ,SUM(T.WSUM_LOAN_BAL     )        AS WSUM_LOAN_BAL        -- 当月余额加权金额
          ,SUM(T.WSUM_LOAN_BAL_LM  )        AS WSUM_LOAN_BAL_LM     -- 上月余额加权金额
          ,SUM(T.WSUM_LOAN_BAL_LY  )        AS WSUM_LOAN_BAL_LY     -- 去年同期余额加权金额
          ,SUM(T.DEP_BAL           )        AS DEP_BAL              -- 当月存款余额
          ,SUM(T.DEP_BAL_LM        )        AS DEP_BAL_LM           -- 上月存款余额
          ,SUM(T.DEP_BAL_LY        )        AS DEP_BAL_LY           -- 去年同期存款余额
          ,SUM(T.WSUM_DEP_BAL      )        AS WSUM_DEP_BAL         -- 当月余额加权金额
          ,SUM(T.WSUM_DEP_BAL_LM   )        AS WSUM_DEP_BAL_LM      -- 上月余额加权金额
          ,SUM(T.WSUM_DEP_BAL_LY   )        AS WSUM_DEP_BAL_LY      -- 去年同期余额加权金额
          ,(CASE WHEN SUM(T.LOAN_BAL)=0  THEN 0
                WHEN SUM(T.LOAN_BAL)='' THEN 0
           ELSE ROUND(SUM(T.WSUM_LOAN_BAL)/SUM(T.LOAN_BAL),5) END)
           -
          (CASE WHEN SUM(T.DEP_BAL)=0  THEN 0
                WHEN SUM(T.DEP_BAL)='' THEN 0
           ELSE ROUND(SUM(T.WSUM_DEP_BAL)/SUM(T.DEP_BAL),5) END)           AS WARL          -- 当月利差
          ,(CASE WHEN SUM(T.LOAN_BAL_LM)=0  THEN 0
                 WHEN SUM(T.LOAN_BAL_LM)='' THEN 0
           ELSE ROUND(SUM(T.WSUM_LOAN_BAL_LM)/SUM(T.LOAN_BAL_LM),5) END)
           -
          (CASE WHEN SUM(T.DEP_BAL_LM)=0  THEN 0
                WHEN SUM(T.DEP_BAL_LM)='' THEN 0
           ELSE ROUND(SUM(T.WSUM_DEP_BAL_LM)/SUM(T.DEP_BAL_LM),5) END)     AS WARL_LM       -- 上月利差
          ,(CASE WHEN SUM(T.LOAN_BAL_LY)=0  THEN 0
                 WHEN SUM(T.LOAN_BAL_LY)='' THEN 0
           ELSE ROUND(SUM(T.WSUM_LOAN_BAL_LY)/SUM(T.LOAN_BAL_LY),5) END)
           -
          (CASE WHEN SUM(T.DEP_BAL_LY)=0  THEN 0
                WHEN SUM(T.DEP_BAL_LY)='' THEN 0
           ELSE ROUND(SUM(T.WSUM_DEP_BAL_LY)/SUM(T.DEP_BAL_LY),5) END)     AS WARL_LY        -- 去年同期利差
    FROM(
        SELECT   YE.FIN_ORG_NO                AS ORG_CODE
                ,YE.CCY                       AS CCY                  --  币种
                ,CASE WHEN YE.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(YE.ACTUAL_BAL,0)/100000000       END       AS LOAN_BAL             -- 当月贷款余额
                ,CASE WHEN YE.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(YE.ACTUAL_BAL,0)/100000000       END       AS LOAN_BAL_LM          -- 上月贷款余额
                ,CASE WHEN YE.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(YE.ACTUAL_BAL,0)/100000000       END       AS LOAN_BAL_LY          -- 去年同期贷款余额
                ,CASE WHEN YE.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(YE.WSUM_BAL,0)/100000000         END       AS WSUM_LOAN_BAL        -- 当月余额加权金额
                ,CASE WHEN YE.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(YE.WSUM_BAL,0)/100000000         END       AS WSUM_LOAN_BAL_LM     -- 上月余额加权金额
                ,CASE WHEN YE.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(YE.WSUM_BAL,0)/100000000         END       AS WSUM_LOAN_BAL_LY     -- 去年同期余额加权金额
                ,0                                                                                                     AS DEP_BAL              -- 当月存款余额
                ,0                                                                                                     AS DEP_BAL_LM           -- 上月存款余额
                ,0                                                                                                     AS DEP_BAL_LY           -- 去年同期存款余额
                ,0                                                                                                     AS WSUM_DEP_BAL         -- 当月余额加权金额
                ,0                                                                                                     AS WSUM_DEP_BAL_LM      -- 上月余额加权金额
                ,0                                                                                                     AS WSUM_DEP_BAL_LY      -- 去年同期余额加权金额
        FROM  EDW.DS_LOAB_SUM  YE
        WHERE YE.DATA_DATE IN('#V_DATA_DATE#' ,'#V_PREV_MONTH_DATE#','#V_PREV_YEAR_DATE#') -- 本月末,上月末,去年同期

        UNION ALL

        SELECT   DEP.FIN_ORG_NO                                                                                          AS ORG_CODE
                ,DEP.CCY                                                                                                 AS CCY                 -- 币种
                ,0                                                                                                       AS LOAN_BAL             -- 当月贷款余额
                ,0                                                                                                       AS LOAN_BAL_LM          -- 上月贷款余额
                ,0                                                                                                       AS LOAN_BAL_LY          -- 去年同期贷款余额
                ,0                                                                                                       AS WSUM_LOAN_BAL        -- 当月余额加权金额
                ,0                                                                                                       AS WSUM_LOAN_BAL_LM     -- 上月余额加权金额
                ,0                                                                                                       AS WSUM_LOAN_BAL_LY     -- 去年同期余额加权金额
                ,CASE WHEN DEP.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(DEP.ACTUAL_BAL,0)/100000000       END       AS DEP_BAL              -- 当月存款余额
                ,CASE WHEN DEP.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(DEP.ACTUAL_BAL,0)/100000000       END       AS DEP_BAL_LM           -- 上月存款余额
                ,CASE WHEN DEP.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(DEP.ACTUAL_BAL,0)/100000000       END       AS DEP_BAL_LY           -- 去年同期存款余额
                ,CASE WHEN DEP.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(DEP.WSUM_BAL,0)/100000000         END       AS WSUM_DEP_BAL         -- 当月余额加权金额
                ,CASE WHEN DEP.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(DEP.WSUM_BAL,0)/100000000         END       AS WSUM_DEP_BAL_LM      -- 上月余额加权金额
                ,CASE WHEN DEP.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(DEP.WSUM_BAL,0)/100000000         END       AS WSUM_DEP_BAL_LY      -- 去年同期余额加权金额
        FROM  EDW.DS_DEPB_SUM  DEP
        WHERE DEP.DATA_DATE IN('#V_DATA_DATE#' ,'#V_PREV_MONTH_DATE#','#V_PREV_YEAR_DATE#') -- 本月末,上月末,去年同期
        )T
    -- 与机构表最细级关联
    LEFT JOIN DIMENSION.T_ORG_BIZ_LVL ORG ON T.ORG_CODE=ORG.ORG_CODE_4 AND ORG.IS_ACTIVE= '1' AND '#V_DATA_DATE#' BETWEEN ORG.START_DATE AND ORG.END_DATE
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON T.ORG_CODE=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    GROUP BY   ORG.ORG_CODE_1
              ,ORG.ORG_DSCR_1
              ,ORG.ORG_CODE_2
              ,ORG.ORG_DSCR_2
              ,ORG.ORG_CODE_3
              ,ORG.ORG_DSCR_3
              ,AREA.AREA_CODE_1
              ,AREA.AREA_DSCR_1
              ,AREA.AREA_CODE_3
              ,AREA.AREA_DSCR_3
              ,AREA.AREA_CODE_4
              ,AREA.AREA_DSCR_4
              ,T.CCY                ;